<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no"><title>大数据之数据仓库Hive02_DDL 与 DML | Oct25-X</title><meta name="keywords" content="Hive"><meta name="author" content="Oct25-X"><meta name="copyright" content="Oct25-X"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="DDL（Data Definition Language）数据定义语言一、数据库（Database）创建数据库 语法  1234CREATE DATABASE [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][WITH DBPROPERTIES (property_name&#x3D;property_va">
<meta property="og:type" content="article">
<meta property="og:title" content="大数据之数据仓库Hive02_DDL 与 DML">
<meta property="og:url" content="https://oct25-x.atomgit.net/oct25-xxxxx/2023/07/16/14%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive02_DDL%20%E5%92%8C%20DML/index.html">
<meta property="og:site_name" content="Oct25-X">
<meta property="og:description" content="DDL（Data Definition Language）数据定义语言一、数据库（Database）创建数据库 语法  1234CREATE DATABASE [IF NOT EXISTS] database_name[COMMENT database_comment][LOCATION hdfs_path][WITH DBPROPERTIES (property_name&#x3D;property_va">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://oct25-x.atomgit.net/oct25-xxxxx/img/cover_img/ctt14.png">
<meta property="article:published_time" content="2023-07-16T01:33:29.000Z">
<meta property="article:modified_time" content="2024-02-17T14:16:59.061Z">
<meta property="article:author" content="Oct25-X">
<meta property="article:tag" content="Hive">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://oct25-x.atomgit.net/oct25-xxxxx/img/cover_img/ctt14.png"><link rel="shortcut icon" href="/oct25-xxxxx/img/logo.jpg"><link rel="canonical" href="https://oct25-x.atomgit.net/oct25-xxxxx/2023/07/16/14%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive02_DDL%20%E5%92%8C%20DML/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/oct25-xxxxx/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@6/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/oct25-xxxxx/',
  algolia: undefined,
  localSearch: {"path":"search.json","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery@2/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: '大数据之数据仓库Hive02_DDL 与 DML',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2024-02-17 22:16:59'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><link rel="stylesheet" href="/css/rightMenu.css"><meta name="generator" content="Hexo 6.1.0"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/oct25-xxxxx/img/avatar.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data is-center"><div class="data-item"><a href="/oct25-xxxxx/archives/"><div class="headline">文章</div><div class="length-num">52</div></a></div><div class="data-item"><a href="/oct25-xxxxx/tags/"><div class="headline">标签</div><div class="length-num">17</div></a></div><div class="data-item"><a href="/oct25-xxxxx/categories/"><div class="headline">分类</div><div class="length-num">8</div></a></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('/oct25-xxxxx/img/top_img/top_bg13.jpg')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/oct25-xxxxx/">Oct25-X</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/oct25-xxxxx/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">大数据之数据仓库Hive02_DDL 与 DML</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2023-07-16T01:33:29.000Z" title="发表于 2023-07-16 09:33:29">2023-07-16</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2024-02-17T14:16:59.061Z" title="更新于 2024-02-17 22:16:59">2024-02-17</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/oct25-xxxxx/categories/%E5%A4%A7%E6%95%B0%E6%8D%AE%E7%BB%84%E4%BB%B6/">大数据组件</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="大数据之数据仓库Hive02_DDL 与 DML"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="DDL（Data-Definition-Language）数据定义语言"><a href="#DDL（Data-Definition-Language）数据定义语言" class="headerlink" title="DDL（Data Definition Language）数据定义语言"></a>DDL（Data Definition Language）数据定义语言</h1><h2 id="一、数据库（Database）"><a href="#一、数据库（Database）" class="headerlink" title="一、数据库（Database）"></a>一、数据库（Database）</h2><h3 id="创建数据库"><a href="#创建数据库" class="headerlink" title="创建数据库"></a>创建数据库</h3><ol>
<li>语法</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> DATABASE [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] database_name</span><br><span class="line">[COMMENT database_comment]</span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[<span class="keyword">WITH</span> DBPROPERTIES (property_name<span class="operator">=</span>property_value, ...)];</span><br></pre></td></tr></table></figure>

<ol start="2">
<li>创建数据库</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database db1;</span><br></pre></td></tr></table></figure>

<ol start="3">
<li>创建数据库，指定路径</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database db2 location <span class="string">&#x27;/db2&#x27;</span>;</span><br></pre></td></tr></table></figure>

<ol start="4">
<li>创建数据库，指定 dbproperties</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database db3 <span class="keyword">with</span></span><br><span class="line">dbproperties(<span class="string">&#x27;create_date&#x27;</span>, <span class="string">&#x27;2023-06-01&#x27;</span>)</span><br></pre></td></tr></table></figure>

<h3 id="查询数据库"><a href="#查询数据库" class="headerlink" title="查询数据库"></a>查询数据库</h3><h4 id="ⅰ查询所有数据库"><a href="#ⅰ查询所有数据库" class="headerlink" title="ⅰ查询所有数据库"></a>ⅰ查询所有数据库</h4><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> DATABASES [<span class="keyword">LIKE</span> <span class="string">&#x27;identifier_with_wildcards&#x27;</span>];</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>查看数据库</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> databases;</span><br></pre></td></tr></table></figure>

<h4 id="ⅱ查看数据库信息"><a href="#ⅱ查看数据库信息" class="headerlink" title="ⅱ查看数据库信息"></a>ⅱ查看数据库信息</h4><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DESCRIBE</span> DATABASE [EXTENDED] db_name;</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>查看数据库信息</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> database db2;</span><br></pre></td></tr></table></figure>

<ol start="3">
<li><strong>查看数据库更多信息</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> database extended db3</span><br></pre></td></tr></table></figure>

<h3 id="修改数据库"><a href="#修改数据库" class="headerlink" title="修改数据库"></a>修改数据库</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--修改dbproperties</span></span><br><span class="line"><span class="keyword">ALTER</span> DATABASE database_name <span class="keyword">SET</span> DBPROPERTIES (property_name<span class="operator">=</span>property_value, ...);</span><br><span class="line"></span><br><span class="line"><span class="comment">--修改location</span></span><br><span class="line"><span class="keyword">ALTER</span> DATABASE database_name <span class="keyword">SET</span> LOCATION hdfs_path;</span><br><span class="line"></span><br><span class="line"><span class="comment">--修改owner user</span></span><br><span class="line"><span class="keyword">ALTER</span> DATABASE database_name <span class="keyword">SET</span> OWNER <span class="keyword">USER</span> user_name;</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>修改数据库信息</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> database db3 <span class="keyword">set</span> dbproperties (<span class="string">&#x27;create_date&#x27;</span><span class="operator">=</span><span class="string">&#x27;2023-05-30&#x27;</span>)</span><br></pre></td></tr></table></figure>

<h3 id="删除数据库"><a href="#删除数据库" class="headerlink" title="删除数据库"></a>删除数据库</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> DATABASE [IF <span class="keyword">EXISTS</span>] database_name [RESTRICT<span class="operator">|</span>CASCADE];</span><br></pre></td></tr></table></figure>

<p>注意：</p>
<p>RESTRICT：严格模式，若数据库不为空，则删除失败，默认为该模式</p>
<p>CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除</p>
<ol start="2">
<li><strong>删除空数据库</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database db1;</span><br></pre></td></tr></table></figure>

<ol start="3">
<li><strong>删除非空数据库</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database db2 cascade;</span><br></pre></td></tr></table></figure>

<h3 id="切换当前数据库"><a href="#切换当前数据库" class="headerlink" title="切换当前数据库"></a>切换当前数据库</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">USE database_name;</span><br></pre></td></tr></table></figure>

<h2 id="二、表（table）"><a href="#二、表（table）" class="headerlink" title="二、表（table）"></a>二、表（table）</h2><h3 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h3><h4 id="ⅰ普通语法"><a href="#ⅰ普通语法" class="headerlink" title="ⅰ普通语法"></a>ⅰ普通语法</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> [TEMPORARY] [<span class="keyword">EXTERNAL</span>] <span class="keyword">TABLE</span> [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] [db_name.]table_name   </span><br><span class="line">[(col_name data_type [COMMENT col_comment], ...)]</span><br><span class="line">[COMMENT table_comment]</span><br><span class="line">[PARTITIONED <span class="keyword">BY</span> (col_name data_type [COMMENT col_comment], ...)]</span><br><span class="line">[CLUSTERED <span class="keyword">BY</span> (col_name, col_name, ...) </span><br><span class="line">[SORTED <span class="keyword">BY</span> (col_name [<span class="keyword">ASC</span><span class="operator">|</span><span class="keyword">DESC</span>], ...)] <span class="keyword">INTO</span> num_buckets BUCKETS]</span><br><span class="line">[<span class="type">ROW</span> FORMAT row_format] </span><br><span class="line">[STORED <span class="keyword">AS</span> file_format]</span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[TBLPROPERTIES (property_name<span class="operator">=</span>property_value, ...)]</span><br></pre></td></tr></table></figure>

<p>参数说明</p>
<p><strong>TEMPORARY</strong>：临时表，表示该表只在当前会话可见，会话结束，表就会被删除</p>
<p><strong>EXTERNAL</strong>（重点）：外部表，与之对应的就是内部表（管理表）。</p>
<ul>
<li>管理表意味着 Hive 会完全接管该表，包括元数据和 HDFS 中的数据。</li>
<li>外部表则意味着Hive只接管元数据，而不完全接管 HDFS 中的数据。</li>
</ul>
<p><strong>data_type</strong>（重点）：Hive 中的字段类型可分为 基本数据类型和复杂数据类型</p>
<p>基本数据类型如下：</p>
<table>
<thead>
<tr>
<th>Hive</th>
<th>说明</th>
<th>定义</th>
</tr>
</thead>
<tbody><tr>
<td>tinyint</td>
<td>1byte有符号整数</td>
<td></td>
</tr>
<tr>
<td>smallint</td>
<td>2byte有符号整数</td>
<td></td>
</tr>
<tr>
<td>int</td>
<td>4byte有符号整数</td>
<td></td>
</tr>
<tr>
<td>bigint</td>
<td>8byte有符号整数</td>
<td></td>
</tr>
<tr>
<td>boolean</td>
<td>布尔类型，true或者false</td>
<td></td>
</tr>
<tr>
<td>float</td>
<td>单精度浮点数</td>
<td></td>
</tr>
<tr>
<td>double</td>
<td>双精度浮点数</td>
<td></td>
</tr>
<tr>
<td>decimal</td>
<td>十进制精准数字类型</td>
<td>decimal(16,2)</td>
</tr>
<tr>
<td>varchar</td>
<td>字符序列，需指定最大长度，最大长度的范围是[1,65535]</td>
<td>varchar(32)</td>
</tr>
<tr>
<td>string</td>
<td>字符串，无需指定最大长度</td>
<td></td>
</tr>
<tr>
<td>timestamp</td>
<td>时间类型</td>
<td></td>
</tr>
<tr>
<td>binary</td>
<td>二进制数据</td>
<td></td>
</tr>
</tbody></table>
<p>复杂数据类型如下：</p>
<table>
<thead>
<tr>
<th>类型</th>
<th>说明</th>
<th>定义</th>
<th>取值</th>
</tr>
</thead>
<tbody><tr>
<td>array</td>
<td>数组是一组相同类型的值的集合</td>
<td>array<string></td>
<td>arr[0]</td>
</tr>
<tr>
<td>map</td>
<td>map是一组相同类型的键-值对集合</td>
<td>map&lt;string, int&gt;</td>
<td>map[‘key’]</td>
</tr>
<tr>
<td>struct</td>
<td>结构体由多个属性组成，每个属性都有自己的属性名和数据类型</td>
<td>struct&lt;id:int,   name:string&gt;</td>
<td>struct.id</td>
</tr>
</tbody></table>
<p>类型转换一：隐式转换</p>
<ul>
<li>任何整数类型都可以隐式地转换为一个范围更广的类型，如 tinyint 可以转换成 int，int 可以转换成 bigint</li>
<li>所有整数类型、float 和 string 类型都可以隐式地转换成 double</li>
<li>tinyint、smallint、int 都可以转换为 float</li>
<li>boolean 类型不可以转换为任何其它的类型</li>
</ul>
<p>类型转换二：显示转换（cast函数）</p>
<ul>
<li>语法</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">cast</span>(expr <span class="keyword">as</span> <span class="operator">&lt;</span>type<span class="operator">&gt;</span>)</span><br></pre></td></tr></table></figure>

<ul>
<li>示例</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hive (<span class="keyword">default</span>)<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="string">&#x27;1&#x27;</span> <span class="operator">+</span> <span class="number">2</span>, <span class="built_in">cast</span>(<span class="string">&#x27;1&#x27;</span> <span class="keyword">as</span> <span class="type">int</span>) <span class="operator">+</span> <span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<p><strong>PARTITIONED BY</strong>（重点）：创建分区</p>
<p><strong>CLUSTERED BY… STORTED BY… INTO… BUCKETS</strong>（重点）：创建分桶表</p>
<p><strong>ROW FORMAT</strong>（重点）：指定SERDE，SERDE 是 Serializer and Deserializer 的简写。Hive 使用 SERDE 序列化和反序列化每行数据</p>
<ul>
<li>语法一：DELIMITED 关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的 SERDE 对每行数据进行序列化和反序列化</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">ROW</span> FORAMT DELIMITED </span><br><span class="line">[FIELDS TERMINATED <span class="keyword">BY</span> <span class="type">char</span>] </span><br><span class="line">[COLLECTION ITEMS TERMINATED <span class="keyword">BY</span> <span class="type">char</span>] </span><br><span class="line">[MAP KEYS TERMINATED <span class="keyword">BY</span> <span class="type">char</span>] </span><br><span class="line">[LINES TERMINATED <span class="keyword">BY</span> <span class="type">char</span>] </span><br><span class="line">[<span class="keyword">NULL</span> DEFINED <span class="keyword">AS</span> <span class="type">char</span>]</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">fields terminated <span class="keyword">by</span>：列分隔符</span><br><span class="line">collection items terminated <span class="keyword">by</span>：map<span class="operator">/</span>struct<span class="operator">/</span><span class="keyword">array</span> 中每个元素之间的分隔符</span><br><span class="line">map keys terminated <span class="keyword">by</span>：map中的key与<span class="keyword">value</span>的分隔符</span><br><span class="line">lines terminated <span class="keyword">by</span>：行分隔符</span><br></pre></td></tr></table></figure>

<ul>
<li>语法二：SERDE 关键字可用于指定其他内置的 SERDE 或者用户自定义的 SERDE。例如 JSONSERDE，可用于处理 JSON 字符串</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="type">ROW</span> FORMAT SERDE serde_name </span><br><span class="line">[<span class="keyword">WITH</span> SERDEPROPERTIES (property_name<span class="operator">=</span>property_value,property_name<span class="operator">=</span>property_value, ...)] </span><br></pre></td></tr></table></figure>

<p><strong>STORED AS</strong>（重点）：指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等</p>
<p><strong>LOCATION</strong>：指定表所对应的 HDFS 路径，若不指定，则使用默认路径</p>
<p><strong>TBLPROPERTIES</strong>：用于配置表的一些 KV 键值对参数</p>
<h4 id="ⅱCTAS-建表语法"><a href="#ⅱCTAS-建表语法" class="headerlink" title="ⅱCTAS 建表语法"></a>ⅱCTAS 建表语法</h4><p>CTAS 即 Create Table As Select，该语法允许用户利用 select 查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含 select 查询语句放回的内容</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> [TEMPORARY] <span class="keyword">TABLE</span> [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] table_name </span><br><span class="line">[COMMENT table_comment] </span><br><span class="line">[<span class="type">ROW</span> FORMAT row_format] </span><br><span class="line">[STORED <span class="keyword">AS</span> file_format] </span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[TBLPROPERTIES (property_name<span class="operator">=</span>property_value, ...)]</span><br><span class="line">[<span class="keyword">AS</span> select_statement]</span><br></pre></td></tr></table></figure>

<h4 id="ⅲCreate-Table-Like-语法"><a href="#ⅲCreate-Table-Like-语法" class="headerlink" title="ⅲCreate Table Like 语法"></a>ⅲCreate Table Like 语法</h4><p>该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> [TEMPORARY] [<span class="keyword">EXTERNAL</span>] <span class="keyword">TABLE</span> [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] [db_name.]table_name</span><br><span class="line">[<span class="keyword">LIKE</span> exist_table_name]</span><br><span class="line">[<span class="type">ROW</span> FORMAT row_format] </span><br><span class="line">[STORED <span class="keyword">AS</span> file_format] </span><br><span class="line">[LOCATION hdfs_path]</span><br><span class="line">[TBLPROPERTIES (property_name<span class="operator">=</span>property_value, ...)]</span><br></pre></td></tr></table></figure>

<h4 id="ⅳ示例"><a href="#ⅳ示例" class="headerlink" title="ⅳ示例"></a>ⅳ示例</h4><ol>
<li><strong>创建内部表</strong></li>
</ol>
<p>Hive 中默认创建的表都是的<strong>内部表</strong>，有时也被称为<strong>管理表</strong>。对于内部表，Hive 会完全管理表的元数据和数据文件。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> if <span class="keyword">not</span> <span class="keyword">exists</span> student(</span><br><span class="line">    id <span class="type">int</span>, </span><br><span class="line">    name string</span><br><span class="line">)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">location <span class="string">&#x27;/user/hive/warehouse/student&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>需要准备数据：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">1001	student1</span><br><span class="line">1002	student2</span><br><span class="line">1003	student3</span><br><span class="line">1004	student4</span><br><span class="line">1005	student5</span><br><span class="line">1006	student6</span><br><span class="line">1007	student7</span><br><span class="line">1008	student8</span><br><span class="line">1009	student9</span><br><span class="line">1010	student10</span><br><span class="line">1011	student11</span><br><span class="line">1012	student12</span><br><span class="line">1013	student13</span><br><span class="line">1014	student14</span><br><span class="line">1015	student15</span><br><span class="line">1016	student16</span><br></pre></td></tr></table></figure>

<p>中间使用 \t 作为分隔符，然后上传到 HDFS上的 &#x2F;user&#x2F;hive&#x2F;warehouse&#x2F;student 目录下</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hadoop fs -put ./student.txt /user/hive/warehouse/student</span><br></pre></td></tr></table></figure>

<p>尝试删除表，然后查看 HDFS 数据文件是否还在</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> student;</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>外部表</strong></li>
</ol>
<p>外部表通常可用于处理其他工具上传的数据文件，对于外部表，Hive 只负责管理元数据，不负责管理 HDFS 中的数据文件</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">external</span> <span class="keyword">table</span> if <span class="keyword">not</span> <span class="keyword">exists</span> student(</span><br><span class="line">    id <span class="type">int</span>, </span><br><span class="line">    name string</span><br><span class="line">)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span></span><br><span class="line">location <span class="string">&#x27;/user/hive/warehouse/student&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>上传数据文件及后面操作与上面内部表相同，最后删除外部表，查看 HDFS 数据文件是否还在</p>
<ol start="3">
<li><strong>SERDE 和 复杂数据类型</strong></li>
</ol>
<p>文件中格式化后数据样例如下：</p>
<figure class="highlight json"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="punctuation">&#123;</span></span><br><span class="line">    <span class="attr">&quot;name&quot;</span><span class="punctuation">:</span> <span class="string">&quot;dasongsong&quot;</span><span class="punctuation">,</span></span><br><span class="line">    <span class="attr">&quot;friends&quot;</span><span class="punctuation">:</span> <span class="punctuation">[</span></span><br><span class="line">        <span class="string">&quot;bingbing&quot;</span><span class="punctuation">,</span></span><br><span class="line">        <span class="string">&quot;lili&quot;</span></span><br><span class="line">    <span class="punctuation">]</span><span class="punctuation">,</span></span><br><span class="line">    <span class="attr">&quot;students&quot;</span><span class="punctuation">:</span> <span class="punctuation">&#123;</span></span><br><span class="line">        <span class="attr">&quot;xiaohaihai&quot;</span><span class="punctuation">:</span> <span class="number">18</span><span class="punctuation">,</span></span><br><span class="line">        <span class="attr">&quot;xiaoyangyang&quot;</span><span class="punctuation">:</span> <span class="number">16</span></span><br><span class="line">    <span class="punctuation">&#125;</span><span class="punctuation">,</span></span><br><span class="line">    <span class="attr">&quot;address&quot;</span><span class="punctuation">:</span> <span class="punctuation">&#123;</span></span><br><span class="line">        <span class="attr">&quot;street&quot;</span><span class="punctuation">:</span> <span class="string">&quot;hui long guan&quot;</span><span class="punctuation">,</span></span><br><span class="line">        <span class="attr">&quot;city&quot;</span><span class="punctuation">:</span> <span class="string">&quot;beijing&quot;</span><span class="punctuation">,</span></span><br><span class="line">        <span class="attr">&quot;postal_code&quot;</span><span class="punctuation">:</span> <span class="number">10010</span></span><br><span class="line">    <span class="punctuation">&#125;</span></span><br><span class="line"><span class="punctuation">&#125;</span></span><br></pre></td></tr></table></figure>

<p>表结构设计如下：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> teacher</span><br><span class="line">(</span><br><span class="line">    name     string,</span><br><span class="line">    friends  <span class="keyword">array</span><span class="operator">&lt;</span>string<span class="operator">&gt;</span>,</span><br><span class="line">    students map<span class="operator">&lt;</span>string,<span class="type">int</span><span class="operator">&gt;</span>,</span><br><span class="line">    address  struct<span class="operator">&lt;</span>city:string,street:string,postal_code:<span class="type">int</span><span class="operator">&gt;</span></span><br><span class="line">)</span><br><span class="line"><span class="type">row</span> format serde <span class="string">&#x27;org.apache.hadoop.hive.serde2.JsonSerDe&#x27;</span></span><br><span class="line">location <span class="string">&#x27;/user/hive/warehouse/teacher&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>准备数据文件</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">&#123;<span class="string">&quot;name&quot;</span>: <span class="string">&quot;zhangsan&quot;</span>,<span class="string">&quot;friends&quot;</span>: [<span class="string">&quot;lisi&quot;</span>,<span class="string">&quot;wangwu&quot;</span>],<span class="string">&quot;students&quot;</span>: &#123;<span class="string">&quot;zhaoliu&quot;</span>: 18,<span class="string">&quot;zhengqi&quot;</span>: 16&#125;,<span class="string">&quot;address&quot;</span>: &#123;<span class="string">&quot;street&quot;</span>: <span class="string">&quot;hui long guan&quot;</span>,<span class="string">&quot;city&quot;</span>: <span class="string">&quot;beijing&quot;</span>,<span class="string">&quot;postal_code&quot;</span>: 10010&#125;&#125;</span><br><span class="line">&#123;<span class="string">&quot;name&quot;</span>: <span class="string">&quot;songba&quot;</span>,<span class="string">&quot;friends&quot;</span>: [<span class="string">&quot;qianjiu&quot;</span>,<span class="string">&quot;liuer&quot;</span>],<span class="string">&quot;students&quot;</span>: &#123;<span class="string">&quot;xuyi&quot;</span>: 18&#125;,<span class="string">&quot;address&quot;</span>: &#123;<span class="string">&quot;street&quot;</span>: <span class="string">&quot;hui long guan&quot;</span>,<span class="string">&quot;city&quot;</span>: <span class="string">&quot;beijing&quot;</span>,<span class="string">&quot;postal_code&quot;</span>: 10010&#125;&#125;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>注意：</p>
<p>数据文件并非真正的 json 文件，每行数据结尾没有逗号。数据文件，每行是一个完整的 json</p>
<p>解析时，按行解析，这样解析到每行都是一个 json 字符串，不会报错</p>
</blockquote>
<p>上传数据文件到 HDFS</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">hadoop fs -put ./teacher.txt /user/hive/warehouse/teacher</span><br></pre></td></tr></table></figure>

<ol start="4">
<li><strong>create table as select 和 create table like</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> teacher1 <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> teacher;</span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> teacher2 <span class="keyword">like</span> teacher;</span><br></pre></td></tr></table></figure>

<h3 id="查看表"><a href="#查看表" class="headerlink" title="查看表"></a>查看表</h3><ol>
<li><strong>查看所有表</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SHOW</span> TABLES [<span class="keyword">IN</span> database_name] <span class="keyword">LIKE</span> [<span class="string">&#x27;identifier_with_wildcards&#x27;</span>];</span><br></pre></td></tr></table></figure>

<p>注意：like 能配表达式说明，* 表示任意字符，|表示或</p>
<ol start="2">
<li><strong>查看表信息</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DESCRIBE</span> [EXTENDED <span class="operator">|</span> FORMATTED] [db_name.]table_name</span><br></pre></td></tr></table></figure>

<p>注意：</p>
<p>EXTENDED：展示详细信息</p>
<p>FORMATTED：格式化展示 </p>
<ol start="3">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> student;</span><br><span class="line"><span class="keyword">desc</span> formatted teacher;</span><br></pre></td></tr></table></figure>

<h3 id="修改表"><a href="#修改表" class="headerlink" title="修改表"></a>修改表</h3><ol>
<li><strong>重命名表语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name RENAME <span class="keyword">TO</span> new_table_name</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>增加列</strong></li>
</ol>
<p>该语句允许用户增加新的列，新增列的位置位于末尾</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name <span class="keyword">ADD</span> COLUMNS (col_name data_type [COMMENT col_comment], ...)</span><br></pre></td></tr></table></figure>

<ol start="3">
<li><strong>更新列</strong></li>
</ol>
<p>该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name CHANGE [<span class="keyword">COLUMN</span>] col_old_name col_new_name column_type [COMMENT col_comment] [<span class="keyword">FIRST</span><span class="operator">|</span>AFTER column_name]</span><br></pre></td></tr></table></figure>

<ol start="4">
<li><strong>替换列</strong></li>
</ol>
<p>该语句允许用户用新的列集替换表中原有的全部列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)</span><br></pre></td></tr></table></figure>

<ol start="5">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--查看表结构</span></span><br><span class="line"><span class="keyword">desc</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">--添加列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student <span class="keyword">add</span> columns(age <span class="type">int</span>);</span><br><span class="line"><span class="comment">--更新列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student change <span class="keyword">column</span> age ages <span class="keyword">double</span>;</span><br><span class="line"><span class="comment">--替换列</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student replace columns(id <span class="type">int</span>, name string)</span><br></pre></td></tr></table></figure>

<h3 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> [IF <span class="keyword">EXISTS</span>] table_name</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> teacher;</span><br></pre></td></tr></table></figure>

<h3 id="清空表"><a href="#清空表" class="headerlink" title="清空表"></a>清空表</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">TRUNCATE</span> [<span class="keyword">TABLE</span>] table_name</span><br></pre></td></tr></table></figure>

<p>注意：truncate只能清空管理表，不能删除外部表中数据</p>
<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> student;</span><br></pre></td></tr></table></figure>

<h1 id="DML（Data-Manipulation-Language）数据操作语言"><a href="#DML（Data-Manipulation-Language）数据操作语言" class="headerlink" title="DML（Data Manipulation Language）数据操作语言"></a>DML（Data Manipulation Language）数据操作语言</h1><h2 id="一、Load"><a href="#一、Load" class="headerlink" title="一、Load"></a>一、Load</h2><p>Load 语句可将文件导入到 Hive 表中</p>
<h3 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">LOAD DATA [<span class="keyword">LOCAL</span>] INPATH <span class="string">&#x27;filepath&#x27;</span> </span><br><span class="line">[OVERWRITE] <span class="keyword">INTO</span> <span class="keyword">TABLE</span> tablename [<span class="keyword">PARTITION</span> (partcol1<span class="operator">=</span>val1, partcol2<span class="operator">=</span>val2 ...)]</span><br></pre></td></tr></table></figure>

<p>关键字说明：</p>
<ul>
<li>LOCAL：表示从本地加载数据到 Hive 表，否则从 HDFS 加载数据</li>
<li>OVERWRITE：表示覆盖表中已有数据，否则表示追加</li>
<li>PARTITION：表示上传到指定分区，若目标是分区表，需指定分区</li>
</ul>
<h3 id="示例"><a href="#示例" class="headerlink" title="示例"></a>示例</h3><ol>
<li><strong>创建表</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student(</span><br><span class="line">    id <span class="type">int</span>, </span><br><span class="line">    name string</span><br><span class="line">) </span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>加载文件到 hive 表</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--加载本地文件</span></span><br><span class="line">load data <span class="keyword">local</span> inpath <span class="string">&#x27;/opt/module/datas/student.txt&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">--加载HDFS文件</span></span><br><span class="line">load data inpath <span class="string">&#x27;/user/hive/warehouse/student&#x27;</span> <span class="keyword">into</span> <span class="keyword">table</span> student;</span><br><span class="line"></span><br><span class="line"><span class="comment">--加载HDFS文件数据，并覆盖表中已有数据</span></span><br><span class="line">load data inpath <span class="string">&#x27;/user/hive/warehouse/student&#x27;</span> overwrite <span class="keyword">into</span> <span class="keyword">table</span> student;</span><br></pre></td></tr></table></figure>

<h2 id="二、Insert"><a href="#二、Insert" class="headerlink" title="二、Insert"></a>二、Insert</h2><h3 id="将查询结果插入到表中"><a href="#将查询结果插入到表中" class="headerlink" title="将查询结果插入到表中"></a>将查询结果插入到表中</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> (<span class="keyword">INTO</span> <span class="operator">|</span> OVERWRITE) <span class="keyword">TABLE</span> tablename [<span class="keyword">PARTITION</span> (partcol1<span class="operator">=</span>val1, partcol2<span class="operator">=</span>val2 ...)] </span><br><span class="line">select_statement;</span><br></pre></td></tr></table></figure>

<p>关键字说明：</p>
<ul>
<li>INTO：将结果追加到目标表</li>
<li>OVERWRITE：用结果覆盖原有数据</li>
</ul>
<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--创建表</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student1(id <span class="type">int</span>, name string)</span><br><span class="line"><span class="type">row</span> format delimited fields terminated <span class="keyword">by</span> <span class="string">&#x27;\t&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--根据查询结果插入数据</span></span><br><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">table</span> student3</span><br><span class="line"><span class="keyword">select</span> id, name <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<h3 id="将给定-values-插入表中"><a href="#将给定-values-插入表中" class="headerlink" title="将给定 values 插入表中"></a>将给定 values 插入表中</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> (<span class="keyword">INTO</span> <span class="operator">|</span> OVERWRITE) <span class="keyword">TABLE</span> tablename [<span class="keyword">PARTITION</span> (partcol1[<span class="operator">=</span>val1], partcol2[<span class="operator">=</span>val2] ...)] </span><br><span class="line"><span class="keyword">VALUES</span> values_row [, values_row ...]</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">table</span> student1 <span class="keyword">values</span> (<span class="number">1</span>, <span class="string">&#x27;zhangsan&#x27;</span>),(<span class="number">2</span>, ;<span class="string">&#x27;lisi&#x27;</span>);</span><br></pre></td></tr></table></figure>

<h3 id="将查询结果写入目标路径"><a href="#将查询结果写入目标路径" class="headerlink" title="将查询结果写入目标路径"></a>将查询结果写入目标路径</h3><ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> OVERWRITE [<span class="keyword">LOCAL</span>] DIRECTORY directory </span><br><span class="line">[<span class="type">ROW</span> FORMAT row_format] [STORED <span class="keyword">AS</span> file_format] </span><br><span class="line">select_statement;</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> overwrite <span class="keyword">local</span> directory <span class="string">&#x27;/opt/module/datas/student&#x27;</span></span><br><span class="line"><span class="type">row</span> format serde <span class="string">&#x27;org.apache.hadoop.hive.serde2.JsonSerDe&#x27;</span></span><br><span class="line"><span class="keyword">select</span> id, name <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<h2 id="三、Export-amp-Import"><a href="#三、Export-amp-Import" class="headerlink" title="三、Export &amp; Import"></a>三、Export &amp; Import</h2><p>Export 导出语句可将表的数据和元数据信息一并导出到 HDFS 路径</p>
<p>Import 可将 Export 导出的内容导入 Hive，表的数据和元数据信息都会恢复。</p>
<p>Export 和 Import 可用于两个 Hive 实例之间的数据迁移。</p>
<ol>
<li><strong>语法</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--导出</span></span><br><span class="line">EXPORT <span class="keyword">TABLE</span> tablename <span class="keyword">TO</span> <span class="string">&#x27;export_target_path&#x27;</span></span><br><span class="line"></span><br><span class="line"><span class="comment">--导入</span></span><br><span class="line">IMPORT [<span class="keyword">EXTERNAL</span>] <span class="keyword">TABLE</span> new_or_original_tablename <span class="keyword">FROM</span> <span class="string">&#x27;source_path&#x27;</span> [LOCATION <span class="string">&#x27;import_target_path&#x27;</span>]</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><strong>示例</strong></li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--导出</span></span><br><span class="line">export <span class="keyword">table</span> default.student <span class="keyword">to</span> <span class="string">&#x27;/user/hive/warehouse/export/student&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">--导入</span></span><br><span class="line">import <span class="keyword">table</span> student2 <span class="keyword">from</span> <span class="string">&#x27;/user/hive/warehouse/export/student&#x27;</span>;</span><br></pre></td></tr></table></figure>





























































</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">Oct25-X</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://oct25-x.atomgit.net/oct25-xxxxx/2023/07/16/14%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive02_DDL%20%E5%92%8C%20DML/">https://oct25-x.atomgit.net/oct25-xxxxx/2023/07/16/14%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive02_DDL%20%E5%92%8C%20DML/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://oct25-x.atomgit.net/oct25-xxxxx" target="_blank">Oct25-X</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/oct25-xxxxx/tags/Hive/">Hive</a></div><div class="post_share"><div class="social-share" data-image="/oct25-xxxxx/img/cover_img/ctt14.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/oct25-xxxxx/2023/07/18/15%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive03_%E6%9F%A5%E8%AF%A2%E5%92%8C%20Hive%20%E5%87%BD%E6%95%B0/"><img class="prev-cover" src="/oct25-xxxxx/img/cover_img/ctt15.png" onerror="onerror=null;src='/oct25-xxxxx/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">大数据之数据仓库Hive03_查询和 Hive 函数</div></div></a></div><div class="next-post pull-right"><a href="/oct25-xxxxx/2023/07/15/13%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive01_%E5%9F%BA%E7%A1%80/"><img class="next-cover" src="/oct25-xxxxx/img/cover_img/ctt13.png" onerror="onerror=null;src='/oct25-xxxxx/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">大数据之数据仓库Hive01_基础</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/oct25-xxxxx/2023/07/15/13%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive01_%E5%9F%BA%E7%A1%80/" title="大数据之数据仓库Hive01_基础"><img class="cover" src="/oct25-xxxxx/img/cover_img/ctt13.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2023-07-15</div><div class="title">大数据之数据仓库Hive01_基础</div></div></a></div><div><a href="/oct25-xxxxx/2023/07/18/15%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive03_%E6%9F%A5%E8%AF%A2%E5%92%8C%20Hive%20%E5%87%BD%E6%95%B0/" title="大数据之数据仓库Hive03_查询和 Hive 函数"><img class="cover" src="/oct25-xxxxx/img/cover_img/ctt15.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2023-07-18</div><div class="title">大数据之数据仓库Hive03_查询和 Hive 函数</div></div></a></div><div><a href="/oct25-xxxxx/2023/07/22/16%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive04_%E5%88%86%E5%8C%BA%E3%80%81%E5%88%86%E6%A1%B6%E8%A1%A8%E4%B8%8E%E5%8E%8B%E7%BC%A9/" title="大数据之数据仓库Hive04_分区/分桶表与压缩"><img class="cover" src="/oct25-xxxxx/img/cover_img/ctt16.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2023-07-22</div><div class="title">大数据之数据仓库Hive04_分区/分桶表与压缩</div></div></a></div><div><a href="/oct25-xxxxx/2023/07/23/17%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93Hive05_%E4%BC%81%E4%B8%9A%E7%BA%A7%E8%B0%83%E4%BC%98/" title="大数据之数据仓库Hive05_企业级调优"><img class="cover" src="/oct25-xxxxx/img/cover_img/ctt17.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2023-07-23</div><div class="title">大数据之数据仓库Hive05_企业级调优</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="/oct25-xxxxx/img/avatar.jpg" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">Oct25-X</div><div class="author-info__description"></div></div><div class="card-info-data is-center"><div class="card-info-data-item"><a href="/oct25-xxxxx/archives/"><div class="headline">文章</div><div class="length-num">52</div></a></div><div class="card-info-data-item"><a href="/oct25-xxxxx/tags/"><div class="headline">标签</div><div class="length-num">17</div></a></div><div class="card-info-data-item"><a href="/oct25-xxxxx/categories/"><div class="headline">分类</div><div class="length-num">8</div></a></div></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#DDL%EF%BC%88Data-Definition-Language%EF%BC%89%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80"><span class="toc-number">1.</span> <span class="toc-text">DDL（Data Definition Language）数据定义语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%88Database%EF%BC%89"><span class="toc-number">1.1.</span> <span class="toc-text">一、数据库（Database）</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.1.</span> <span class="toc-text">创建数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.2.</span> <span class="toc-text">查询数据库</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B0%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.2.1.</span> <span class="toc-text">ⅰ查询所有数据库</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B1%E6%9F%A5%E7%9C%8B%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BF%A1%E6%81%AF"><span class="toc-number">1.1.2.2.</span> <span class="toc-text">ⅱ查看数据库信息</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.3.</span> <span class="toc-text">修改数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.4.</span> <span class="toc-text">删除数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%87%E6%8D%A2%E5%BD%93%E5%89%8D%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.1.5.</span> <span class="toc-text">切换当前数据库</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E8%A1%A8%EF%BC%88table%EF%BC%89"><span class="toc-number">1.2.</span> <span class="toc-text">二、表（table）</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E8%A1%A8"><span class="toc-number">1.2.1.</span> <span class="toc-text">创建表</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B0%E6%99%AE%E9%80%9A%E8%AF%AD%E6%B3%95"><span class="toc-number">1.2.1.1.</span> <span class="toc-text">ⅰ普通语法</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B1CTAS-%E5%BB%BA%E8%A1%A8%E8%AF%AD%E6%B3%95"><span class="toc-number">1.2.1.2.</span> <span class="toc-text">ⅱCTAS 建表语法</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B2Create-Table-Like-%E8%AF%AD%E6%B3%95"><span class="toc-number">1.2.1.3.</span> <span class="toc-text">ⅲCreate Table Like 语法</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E2%85%B3%E7%A4%BA%E4%BE%8B"><span class="toc-number">1.2.1.4.</span> <span class="toc-text">ⅳ示例</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%9F%A5%E7%9C%8B%E8%A1%A8"><span class="toc-number">1.2.2.</span> <span class="toc-text">查看表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BF%AE%E6%94%B9%E8%A1%A8"><span class="toc-number">1.2.3.</span> <span class="toc-text">修改表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%88%A0%E9%99%A4%E8%A1%A8"><span class="toc-number">1.2.4.</span> <span class="toc-text">删除表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E6%B8%85%E7%A9%BA%E8%A1%A8"><span class="toc-number">1.2.5.</span> <span class="toc-text">清空表</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#DML%EF%BC%88Data-Manipulation-Language%EF%BC%89%E6%95%B0%E6%8D%AE%E6%93%8D%E4%BD%9C%E8%AF%AD%E8%A8%80"><span class="toc-number">2.</span> <span class="toc-text">DML（Data Manipulation Language）数据操作语言</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%80%E3%80%81Load"><span class="toc-number">2.1.</span> <span class="toc-text">一、Load</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E8%AF%AD%E6%B3%95"><span class="toc-number">2.1.1.</span> <span class="toc-text">语法</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%A4%BA%E4%BE%8B"><span class="toc-number">2.1.2.</span> <span class="toc-text">示例</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8C%E3%80%81Insert"><span class="toc-number">2.2.</span> <span class="toc-text">二、Insert</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B0%86%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E6%8F%92%E5%85%A5%E5%88%B0%E8%A1%A8%E4%B8%AD"><span class="toc-number">2.2.1.</span> <span class="toc-text">将查询结果插入到表中</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B0%86%E7%BB%99%E5%AE%9A-values-%E6%8F%92%E5%85%A5%E8%A1%A8%E4%B8%AD"><span class="toc-number">2.2.2.</span> <span class="toc-text">将给定 values 插入表中</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%B0%86%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E5%86%99%E5%85%A5%E7%9B%AE%E6%A0%87%E8%B7%AF%E5%BE%84"><span class="toc-number">2.2.3.</span> <span class="toc-text">将查询结果写入目标路径</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%89%E3%80%81Export-amp-Import"><span class="toc-number">2.3.</span> <span class="toc-text">三、Export &amp; Import</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/oct25-xxxxx/2024/02/24/52%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala09_%E9%9A%90%E5%BC%8F%E8%BD%AC%E6%8D%A2%E3%80%81%E5%BC%82%E5%B8%B8%E5%92%8C%E6%B3%9B%E5%9E%8B/" title="大数据之多范式编程语言 Scala09_隐式转换、异常和泛型"><img src="/oct25-xxxxx/img/cover_img/ctt52.png" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/404.jpg'" alt="大数据之多范式编程语言 Scala09_隐式转换、异常和泛型"/></a><div class="content"><a class="title" href="/oct25-xxxxx/2024/02/24/52%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala09_%E9%9A%90%E5%BC%8F%E8%BD%AC%E6%8D%A2%E3%80%81%E5%BC%82%E5%B8%B8%E5%92%8C%E6%B3%9B%E5%9E%8B/" title="大数据之多范式编程语言 Scala09_隐式转换、异常和泛型">大数据之多范式编程语言 Scala09_隐式转换、异常和泛型</a><time datetime="2024-02-24T04:55:34.000Z" title="发表于 2024-02-24 12:55:34">2024-02-24</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/oct25-xxxxx/2024/02/24/51%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala08_%E6%A8%A1%E5%BC%8F%E5%8C%B9%E9%85%8D/" title="大数据之多范式编程语言 Scala08_模式匹配"><img src="/oct25-xxxxx/img/cover_img/ctt51.png" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/404.jpg'" alt="大数据之多范式编程语言 Scala08_模式匹配"/></a><div class="content"><a class="title" href="/oct25-xxxxx/2024/02/24/51%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala08_%E6%A8%A1%E5%BC%8F%E5%8C%B9%E9%85%8D/" title="大数据之多范式编程语言 Scala08_模式匹配">大数据之多范式编程语言 Scala08_模式匹配</a><time datetime="2024-02-24T00:46:12.000Z" title="发表于 2024-02-24 08:46:12">2024-02-24</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/oct25-xxxxx/2024/02/22/50%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala07_%E9%9B%86%E5%90%88/" title="大数据之多范式编程语言 Scala07_集合"><img src="/oct25-xxxxx/img/cover_img/ctt50.png" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/404.jpg'" alt="大数据之多范式编程语言 Scala07_集合"/></a><div class="content"><a class="title" href="/oct25-xxxxx/2024/02/22/50%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala07_%E9%9B%86%E5%90%88/" title="大数据之多范式编程语言 Scala07_集合">大数据之多范式编程语言 Scala07_集合</a><time datetime="2024-02-22T08:16:10.000Z" title="发表于 2024-02-22 16:16:10">2024-02-22</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/oct25-xxxxx/2024/02/21/49%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala06_%E9%9D%A2%E5%90%91%E5%AF%B9%E8%B1%A1/" title="大数据之多范式编程语言 Scala06_面向对象"><img src="/oct25-xxxxx/img/cover_img/ctt49.png" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/404.jpg'" alt="大数据之多范式编程语言 Scala06_面向对象"/></a><div class="content"><a class="title" href="/oct25-xxxxx/2024/02/21/49%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala06_%E9%9D%A2%E5%90%91%E5%AF%B9%E8%B1%A1/" title="大数据之多范式编程语言 Scala06_面向对象">大数据之多范式编程语言 Scala06_面向对象</a><time datetime="2024-02-21T07:01:10.000Z" title="发表于 2024-02-21 15:01:10">2024-02-21</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/oct25-xxxxx/2024/02/20/48%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala05_%E5%87%BD%E6%95%B0%E5%BC%8F%E7%BC%96%E7%A8%8B/" title="大数据之多范式编程语言 Scala05_函数式编程"><img src="/oct25-xxxxx/img/cover_img/ctt48.png" onerror="this.onerror=null;this.src='/oct25-xxxxx/img/404.jpg'" alt="大数据之多范式编程语言 Scala05_函数式编程"/></a><div class="content"><a class="title" href="/oct25-xxxxx/2024/02/20/48%E5%A4%A7%E6%95%B0%E6%8D%AE%E4%B9%8B%E5%A4%9A%E8%8C%83%E5%BC%8F%E7%BC%96%E7%A8%8B%E8%AF%AD%E8%A8%80Scala05_%E5%87%BD%E6%95%B0%E5%BC%8F%E7%BC%96%E7%A8%8B/" title="大数据之多范式编程语言 Scala05_函数式编程">大数据之多范式编程语言 Scala05_函数式编程</a><time datetime="2024-02-20T12:05:11.000Z" title="发表于 2024-02-20 20:05:11">2024-02-20</time></div></div></div></div></div></div></main><footer id="footer" style="background-image: url('/oct25-xxxxx/img/top_img/top_bg13.jpg')"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2024 By Oct25-X</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">本地搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="is-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据库加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div id="local-search-results"></div></div></div><div id="search-mask"></div></div><div id="rightMenu"><div class="rightMenu-group rightMenu-small"><div class="rightMenu-item" id="menu-backward"><i class="fa-solid fa-arrow-left"></i></div><div class="rightMenu-item" id="menu-forward"><i class="fa-solid fa-arrow-right"></i></div><div class="rightMenu-item" id="menu-refresh"><i class="fa-solid fa-arrow-rotate-right"></i></div><div class="rightMenu-item" id="menu-home"><i class="fa-solid fa-house"></i></div></div><div class="rightMenu-group rightMenu-line rightMenuOther"><a class="rightMenu-item menu-link" href="/archives/"><i class="fa-solid fa-archive"></i><span>文章归档</span></a><a class="rightMenu-item menu-link" href="/categories/"><i class="fa-solid fa-folder-open"></i><span>文章分类</span></a><a class="rightMenu-item menu-link" href="/tags/"><i class="fa-solid fa-tags"></i><span>文章标签</span></a></div><div class="rightMenu-group rightMenu-line rightMenuNormal"><a class="rightMenu-item" id="menu-translate"><i class="fa-solid fa-earth-asia"></i><span>繁简切换</span></a><div class="rightMenu-item" id="menu-darkmode"><i class="fa-solid fa-moon"></i><span>切换模式</span></div></div></div><div id="rightmenu-mask"></div><div><script src="/oct25-xxxxx/js/utils.js"></script><script src="/oct25-xxxxx/js/main.js"></script><script src="/oct25-xxxxx/js/tw_cn.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.js"></script><script src="/oct25-xxxxx/js/search/local-search.js"></script><div class="js-pjax"></div><script defer src="https://npm.elemecdn.com/jquery@latest/dist/jquery.min.js"></script><script defer data-pjax src="/js/rightMenu.js"></script><script defer="defer" id="ribbon" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/canvas-ribbon.min.js" size="150" alpha="0.6" zIndex="-1" mobile="false" data-click="true"></script><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/activate-power-mode.min.js"></script><script>POWERMODE.colorful = true;
POWERMODE.shake = true;
POWERMODE.mobile = false;
document.body.addEventListener('input', POWERMODE);
</script><script id="click-heart" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/click-heart.min.js" async="async" mobile="false"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>